﻿using YouWei.Utility.Logging;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace YouWei.Utility
{
    /// <summary>
    /// 单表数据提供器，可以简化对单表数据的查询操作，提供了
    /// </summary>
    public abstract class OneTableDataProvider
    {
        public static T Create<T>(IDataRecord dr)
        {
            Type modelType = typeof(T);
            T model;

            if (modelType.IsValueType || modelType.ToString() == "System.String") //处理值类型和string
            {
                return (T)dr[0];
            }

            model = Activator.CreateInstance<T>();

            for (int i = 0; i < dr.FieldCount; i++)
            {
                if (dr.GetValue(i) == DBNull.Value) continue;
                PropertyInfo pi = modelType.GetProperty(dr.GetName(i), BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance);
                if (pi == null) continue; //实体对象中没有这个属性

                if (pi.PropertyType.IsEnum)//枚举
                {
                    pi.SetValue(model, Enum.Parse(pi.PropertyType, dr.GetValue(i).ToString()), null);
                    continue;
                }

                Type type = pi.PropertyType;

                //特殊类型,可空类型
                if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                    type = Nullable.GetUnderlyingType(pi.PropertyType);
                if (dr.GetValue(i).GetType().FullName == "System.Guid" && type.FullName == "System.String")
                {
                    pi.SetValue(model, dr.GetValue(i).ToString(), null);
                }
                else
                {
                    pi.SetValue(model, Convert.ChangeType(dr.GetValue(i), type), null);
                }

            }
            return model;
        }

        #region 根据断言获取对象及列表信息
        /// <summary>
        /// 根据断言获取对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public static T CreateObject<T>(string conn, Expression<Func<T, bool>> predicate)
        {
            if (!conn.Contains("="))
            {
                conn = ConfigurationManager.ConnectionStrings[conn].ConnectionString;
                //conn = DesHelper.DecryptDes(conn);
            }
            Type modelType = typeof(T);
            var tableName = modelType.Name;
            if (SomeExtension.Right(tableName,4) == "Info")
            {
                tableName = tableName.Substring(0, tableName.Length - 4);
            }
            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"SELECT top 1 * FROM {tableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = "1=1";
            }
            szSql = szSql + whereClause;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(conn, CommandType.Text, szSql))
            {
                if (dr.Read())
                {
                    return Create<T>(dr);
                }
            }
            return default(T);
        }
        /// <summary>
        /// 根据断言获取对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public static List<T> CreateList<T>(string conn, Expression<Func<T, bool>> predicate)
        {
            if (!conn.Contains("="))
            {
                conn = ConfigurationManager.ConnectionStrings[conn].ConnectionString;
                //conn = DesHelper.DecryptDes(conn);
            }
            List<T> list = new List<T>();
            Type modelType = typeof(T);
            var tableName = modelType.Name;
            if (SomeExtension.Right(tableName, 4) == "Info")
            {
                tableName = tableName.Substring(0, tableName.Length - 4);
            }
            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"SELECT * FROM {tableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = "1=1";
            }
            if (whereClause == "1")
            {
                whereClause = "1=1";
            }
            szSql = szSql + whereClause;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(conn, CommandType.Text, szSql))
            {
                while (dr.Read())
                {
                    T newt = Create<T>(dr);
                    if (newt != null)
                        list.Add(newt);
                }
            }
            return list;
        }

        #endregion

    }

    public class QueryTranslator : ExpressionVisitor
    {
        private StringBuilder sb;
        private string _orderBy = string.Empty;
        private int? _skip = null;
        private int? _take = null;
        private string _whereClause = string.Empty;
        private List<SqlParameter> sqlParams = new List<SqlParameter>();

        public int? Skip
        {
            get
            {
                return _skip;
            }
        }

        public int? Take
        {
            get
            {
                return _take;
            }
        }

        public string OrderBy
        {
            get
            {
                return _orderBy;
            }
        }

        public string WhereClause
        {
            get
            {
                return _whereClause;
            }
        }

        public QueryTranslator()
        {

        }

        public string Translate<T>(Expression expression)
        {
            this.sb = new StringBuilder();
            this.Visit(expression);
            _whereClause = this.sb.ToString();
            return _whereClause;
        }
        public string Translate<T>(Expression<Func<T, bool>> predicate)
        {
            this.sb = new StringBuilder();
            this.Visit(predicate);
            _whereClause = this.sb.ToString();

            return _whereClause;
        }

        private static Expression StripQuotes(Expression e)
        {
            while (e.NodeType == ExpressionType.Quote)
            {
                e = ((UnaryExpression)e).Operand;
            }
            return e;
        }

        protected override Expression VisitMethodCall(MethodCallExpression m)
        {
            if (m.Method.DeclaringType == typeof(Queryable) && m.Method.Name == "Where")
            {
                this.Visit(m.Arguments[0]);
                LambdaExpression lambda = (LambdaExpression)StripQuotes(m.Arguments[1]);
                this.Visit(lambda.Body);
                return m;
            }
            else if (m.Method.Name == "Take")
            {
                if (this.ParseTakeExpression(m))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }
            else if (m.Method.Name == "Skip")
            {
                if (this.ParseSkipExpression(m))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }
            else if (m.Method.Name == "OrderBy")
            {
                if (this.ParseOrderByExpression(m, "ASC"))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }
            else if (m.Method.Name == "OrderByDescending")
            {
                if (this.ParseOrderByExpression(m, "DESC"))
                {
                    Expression nextExpression = m.Arguments[0];
                    return this.Visit(nextExpression);
                }
            }

            throw new NotSupportedException(string.Format("The method '{0}' is not supported", m.Method.Name));
        }

        protected override Expression VisitUnary(UnaryExpression u)
        {
            switch (u.NodeType)
            {
                case ExpressionType.Not:
                    sb.Append(" NOT ");
                    this.Visit(u.Operand);
                    break;
                case ExpressionType.Convert:
                    this.Visit(u.Operand);
                    break;
                default:
                    throw new NotSupportedException(string.Format("The unary operator '{0}' is not supported", u.NodeType));
            }
            return u;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        protected override Expression VisitBinary(BinaryExpression b)
        {
            sb.Append("(");
            this.Visit(b.Left);

            switch (b.NodeType)
            {
                case ExpressionType.And:
                    sb.Append(" AND ");
                    break;

                case ExpressionType.AndAlso:
                    sb.Append(" AND ");
                    break;

                case ExpressionType.Or:
                    sb.Append(" OR ");
                    break;

                case ExpressionType.OrElse:
                    sb.Append(" OR ");
                    break;

                case ExpressionType.Equal:
                    if (IsNullConstant(b.Right))
                    {
                        sb.Append(" IS ");
                    }
                    else
                    {
                        sb.Append(" = ");
                    }
                    break;

                case ExpressionType.NotEqual:
                    if (IsNullConstant(b.Right))
                    {
                        sb.Append(" IS NOT ");
                    }
                    else
                    {
                        sb.Append(" <> ");
                    }
                    break;

                case ExpressionType.LessThan:
                    sb.Append(" < ");
                    break;

                case ExpressionType.LessThanOrEqual:
                    sb.Append(" <= ");
                    break;

                case ExpressionType.GreaterThan:
                    sb.Append(" > ");
                    break;

                case ExpressionType.GreaterThanOrEqual:
                    sb.Append(" >= ");
                    break;

                default:
                    throw new NotSupportedException(string.Format("The binary operator '{0}' is not supported", b.NodeType));

            }

            this.Visit(b.Right);
            sb.Append(")");
            return b;
        }

        protected override Expression VisitConstant(ConstantExpression c)
        {
            IQueryable q = c.Value as IQueryable;

            if (q == null && c.Value == null)
            {
                sb.Append("NULL");
            }
            else if (q == null)
            {
                switch (Type.GetTypeCode(c.Value.GetType()))
                {
                    case TypeCode.Boolean:
                        sb.Append(((bool)c.Value) ? 1 : 0);
                        break;

                    case TypeCode.String:
                        sb.Append("'");
                        sb.Append(SomeExtension.SqlFilter(c.Value.ToString()));
                        sb.Append("'");
                        break;

                    case TypeCode.DateTime:
                        sb.Append("'");
                        //sb.Append(c.Value);
                        sb.Append(Convert.ToDateTime(c.Value).ToString("yyyy-MM-dd HH:mm:ss"));
                        sb.Append("'");
                        break;

                    case TypeCode.Object:
                        throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", c.Value));

                    default:
                        sb.Append(SomeExtension.SqlFilter(c.Value.ToString()));
                        break;
                }
            }

            return c;
        }

        protected override Expression VisitMember(MemberExpression m)
        {
            if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
            {
                sb.Append(m.Member.Name);
                return m;
            }
            else if (m.Expression != null && m.Expression.NodeType == ExpressionType.Constant)
            {
                // Recurse down to see if we can simplify...
                var expression = m.Expression;
                object container = ((ConstantExpression)expression).Value;
                var member = m.Member;
                if (member is FieldInfo)
                {
                    object value = ((FieldInfo)member).GetValue(container); ;
                    //return Expression.Constant(value);
                    return VisitConstant(Expression.Constant(value));
                }
                if (member is PropertyInfo)
                {
                    object value = ((PropertyInfo)member).GetValue(container, null);
                    //return Expression.Constant(value);
                    return VisitConstant(Expression.Constant(value));
                }
            }
            else if (m.Expression != null && m.Expression.NodeType == ExpressionType.MemberAccess)
            {
                //MemberExpression outerMember = (MemberExpression)m.Expression;
                //PropertyInfo outerProp = (PropertyInfo)outerMember.Member;
                //MemberExpression innerMember = (MemberExpression)outerMember.Expression;
                //FieldInfo innerField = (FieldInfo)innerMember.Member;
                //ConstantExpression ce = (ConstantExpression)innerMember.Expression;
                //object innerObj = ce.Value;
                //object outerObj = innerField.GetValue(innerObj);
                var objectMember = Expression.Convert(m, typeof(object));
                var getterLambda = Expression.Lambda<Func<object>>(objectMember);
                var getter = getterLambda.Compile();
                object value = getter();
                return VisitConstant(Expression.Constant(value));
            }


            throw new NotSupportedException(string.Format("The member '{0}' is not supported", m.Member.Name));
        }

        protected bool IsNullConstant(Expression exp)
        {
            return (exp.NodeType == ExpressionType.Constant && ((ConstantExpression)exp).Value == null);
        }

        private bool ParseOrderByExpression(MethodCallExpression expression, string order)
        {
            UnaryExpression unary = (UnaryExpression)expression.Arguments[1];
            LambdaExpression lambdaExpression = (LambdaExpression)unary.Operand;

            lambdaExpression = (LambdaExpression)Evaluator.PartialEval(lambdaExpression);

            MemberExpression body = lambdaExpression.Body as MemberExpression;
            if (body != null)
            {
                if (string.IsNullOrEmpty(_orderBy))
                {
                    _orderBy = string.Format("{0} {1}", body.Member.Name, order);
                }
                else
                {
                    _orderBy = string.Format("{0}, {1} {2}", _orderBy, body.Member.Name, order);
                }

                return true;
            }

            return false;
        }

        private bool ParseTakeExpression(MethodCallExpression expression)
        {
            ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];

            int size;
            if (int.TryParse(sizeExpression.Value.ToString(), out size))
            {
                _take = size;
                return true;
            }

            return false;
        }

        private bool ParseSkipExpression(MethodCallExpression expression)
        {
            ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];

            int size;
            if (int.TryParse(sizeExpression.Value.ToString(), out size))
            {
                _skip = size;
                return true;
            }

            return false;
        }
    }
    
    public static class Evaluator
    {

        /// <summary>
        /// Performs evaluation & replacement of independent sub-trees
        /// </summary>
        /// <param name="expression">The root of the expression tree.</param>
        /// <param name="fnCanBeEvaluated">A function that decides whether a given expression node can be part of the local function.</param>
        /// <returns>A new tree with sub-trees evaluated and replaced.</returns>
        public static Expression PartialEval(Expression expression, Func<Expression, bool> fnCanBeEvaluated)
        {
            return new SubtreeEvaluator(new Nominator(fnCanBeEvaluated).Nominate(expression)).Eval(expression);

        }



        /// <summary>
        /// Performs evaluation & replacement of independent sub-trees
        /// </summary>
        /// <param name="expression">The root of the expression tree.</param>
        /// <returns>A new tree with sub-trees evaluated and replaced.</returns>

        public static Expression PartialEval(Expression expression)
        {

            return PartialEval(expression, Evaluator.CanBeEvaluatedLocally);

        }

        private static bool CanBeEvaluatedLocally(Expression expression)
        {

            return expression.NodeType != ExpressionType.Parameter;

        }



        /// <summary>
        /// Evaluates & replaces sub-trees when first candidate is reached (top-down)
        /// </summary>
        class SubtreeEvaluator : ExpressionVisitor
        {

            HashSet<Expression> candidates;



            internal SubtreeEvaluator(HashSet<Expression> candidates)
            {

                this.candidates = candidates;

            }



            internal Expression Eval(Expression exp)
            {

                return this.Visit(exp);

            }



            public override Expression Visit(Expression exp)
            {

                if (exp == null)
                {

                    return null;

                }

                if (this.candidates.Contains(exp))
                {

                    return this.Evaluate(exp);

                }

                return base.Visit(exp);

            }



            private Expression Evaluate(Expression e)
            {

                if (e.NodeType == ExpressionType.Constant)
                {

                    return e;

                }

                LambdaExpression lambda = Expression.Lambda(e);

                Delegate fn = lambda.Compile();

                return Expression.Constant(fn.DynamicInvoke(null), e.Type);

            }

        }



        /// <summary>
        /// Performs bottom-up analysis to determine which nodes can possibly
        /// be part of an evaluated sub-tree.
        /// </summary>

        class Nominator : ExpressionVisitor
        {

            Func<Expression, bool> fnCanBeEvaluated;

            HashSet<Expression> candidates;

            bool cannotBeEvaluated;



            internal Nominator(Func<Expression, bool> fnCanBeEvaluated)
            {

                this.fnCanBeEvaluated = fnCanBeEvaluated;

            }



            internal HashSet<Expression> Nominate(Expression expression)
            {

                this.candidates = new HashSet<Expression>();

                this.Visit(expression);

                return this.candidates;

            }



            public override Expression Visit(Expression expression)
            {
                if (expression != null)
                {
                    bool saveCannotBeEvaluated = this.cannotBeEvaluated;

                    this.cannotBeEvaluated = false;

                    base.Visit(expression);

                    if (!this.cannotBeEvaluated)
                    {

                        if (this.fnCanBeEvaluated(expression))
                        {

                            this.candidates.Add(expression);

                        }

                        else
                        {

                            this.cannotBeEvaluated = true;

                        }

                    }

                    this.cannotBeEvaluated |= saveCannotBeEvaluated;

                }

                return expression;

            }

        }

    }

    public static class SomeExtension
    {
        /// <summary>
        /// 过滤字符串中注入SQL脚本的方法
        /// </summary>
        /// <param name="source">传入的字符串</param>
        /// <returns>过滤后的字符串</returns>
        public static string SqlFilter(string source)
        {
            //半角括号替换为全角括号
            source = source.Replace("'", "''").Replace(";", "；").Replace("(", "（").Replace(")", "）");

            //去除执行SQL语句的命令关键字
            source = Regex.Replace(source, "select", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "insert", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "update", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "delete", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "drop", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "truncate", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "declare", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "xp_cmdshell", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "/add", "", RegexOptions.IgnoreCase);
            //Regex.Replace(source, "asc(", "", RegexOptions.IgnoreCase);
            //Regex.Replace(source, "mid(", "", RegexOptions.IgnoreCase);
            //Regex.Replace(source, "char(", "", RegexOptions.IgnoreCase);
            //Regex.Replace(source, "count(", "", RegexOptions.IgnoreCase);
            //fetch 
            //IS_SRVROLEMEMBER
            //Cast(

            source = Regex.Replace(source, "net user", "", RegexOptions.IgnoreCase);

            //去除执行存储过程的命令关键字 
            source = Regex.Replace(source, "exec", "", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "execute", "", RegexOptions.IgnoreCase);

            //去除系统存储过程或扩展存储过程关键字
            source = Regex.Replace(source, "xp_", "x p_", RegexOptions.IgnoreCase);
            source = Regex.Replace(source, "sp_", "s p_", RegexOptions.IgnoreCase);

            //防止16进制注入
            source = Regex.Replace(source, "0x", "0 x", RegexOptions.IgnoreCase);

            return source;
        }

        #region 获取字符串右边起指定个数的子字符串

        /// <summary>
        /// 获取字符串右边起指定个数的子字符串
        /// </summary>
        /// <param name="Input">要获取其右边指定个数子字符串的源字符串</param>
        /// <param name="Length">要获取其左边指定个数子字符串的源字符串</param>
        /// <returns></returns>
        public static string Right(string Input, int Length)
        {
            if (string.IsNullOrEmpty(Input))
                return "";
            Length = Input.Length > Length ? Length : Input.Length;
            return Input.Substring(Input.Length - Length, Length);
        }

        #endregion

    }


    /// <summary>
    /// 抽象类，集成访问SQL SERVER数据库的方法
    /// </summary>
    public abstract class SQLHelper
    {
        //数据库连接字符串
        //public static readonly string ConnectionString = ConstantUtility.ConnectionString;

        //哈希表用来存贮参数
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 执行SQL命令，不返回记录集，返回影响的记录行数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandSqlParameters">SQL参数组</param>
        /// <returns>［int］受影响的记录行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandSqlParameters)
        {
            SqlCommand com = new SqlCommand();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                PrepareCommand(com, con, null, cmdType, cmdText, commandSqlParameters);
                int val = com.ExecuteNonQuery();
                com.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行SQL命令，不返回记录集，返回影响的记录行数
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符口串</param>
        /// <param name="commandSqlParameters">SQL参数组</param>
        /// <returns>［int］受影响的记录数</returns>
        public static int ExecuteNonQuery(SqlConnection con, CommandType cmdType, string cmdText, params SqlParameter[] commandSqlParameters)
        {

            SqlCommand com = new SqlCommand();

            PrepareCommand(com, con, null, cmdType, cmdText, commandSqlParameters);
            int val = com.ExecuteNonQuery();
            com.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行SQL命令(事务方式)，不返回记录集，返回影响的记录行数
        /// </summary>
        /// <param name="trans">事务名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandSqlParameters">SQL参数组</param>
        /// <returns>［int］受影响的记录数</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandSqlParameters)
        {
            SqlCommand com = new SqlCommand();
            PrepareCommand(com, trans.Connection, trans, cmdType, cmdText, commandSqlParameters);
            int val = com.ExecuteNonQuery();
            com.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行SQL命令，返回记录集
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandSqlParameters">SQL参数组</param>
        /// <returns>［SqlDataReader］返回记录集</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandSqlParameters)
        {

            SqlCommand com = new SqlCommand();
            SqlConnection con = new SqlConnection(connectionString);

            //使用异常处理方式，如果SqlDataReader存在，则关闭连接

            try
            {
                PrepareCommand(com, con, null, cmdType, cmdText, commandSqlParameters);
                SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection);
                com.Parameters.Clear();
                return dr;
            }
            catch (Exception ex)
            {
                Log4netLog.Error(ex);
                con.Close();
                throw;
            }

        }

        /// <summary>
        /// 执行带返回值的SQL命令
        /// </summary>
        /// <param name="connectionString">数据库连接串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandSqlParameters">SQL参数组</param>
        /// <returns>[object]返回object类型</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandSqlParameters)
        {

            SqlCommand com = new SqlCommand();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                PrepareCommand(com, con, null, cmdType, cmdText, commandSqlParameters);
                object val = com.ExecuteScalar();
                com.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行带返回值的SQL命令
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandSqlParameters">SQL参数组</param>
        /// <returns>[object]返回object类型</returns>
        public static object ExecuteScalar(SqlConnection con, CommandType cmdType, string cmdText, params SqlParameter[] commandSqlParameters)
        {

            SqlCommand com = new SqlCommand();
            PrepareCommand(com, con, null, cmdType, cmdText, commandSqlParameters);
            object val = com.ExecuteScalar();
            com.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 将SQL参数组存放到缓存中
        /// </summary>
        /// <param name="cacheKey">缓存关键字</param>
        /// <param name="commandSqlParameters">SQL参数组</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandSqlParameters)
        {
            parmCache[cacheKey] = commandSqlParameters;
        }

        /// <summary>
        /// 取出缓存中的SQL参数组
        /// </summary>
        /// <param name="cacheKey">缓存关键字</param>
        /// <returns>［SqlParameter[]］ 返回SQL参数数组</returns>
        public static SqlParameter[] GetCacheParameters(string cacheKey)
        {
            SqlParameter[] cacheParms = (SqlParameter[])parmCache[cacheKey];

            if (cacheParms == null)
            {
                return null;
            }

            SqlParameter[] cloneParms = new SqlParameter[cacheParms.Length];

            for (int i = 0, j = cacheParms.Length; i < j; i++)
            {

                cloneParms[i] = (SqlParameter)((ICloneable)cacheParms[i]).Clone();
            }
            return cloneParms;

        }

        private static void PrepareCommand(SqlCommand com, SqlConnection con, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (con.State != ConnectionState.Open)
                con.Open();

            com.Connection = con;
            com.CommandText = cmdText;
            com.CommandType = cmdType;
            if (trans != null)
                com.Transaction = trans;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {

                    com.Parameters.Add(parm);
                }
            }
        }
    }
}
